package weaver.splittable.db.sqlserver;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.WeaverConnection;
import weaver.conn.WeaverStatement;
import weaver.splittable.db.STCreateTableUtil;
import weaver.splittable.db.STDBConstants;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * 建表工具的sqlserver实现
 * author: YUEYANG 2023-01-12
 */
public class STSqlserverCreateTableUtil implements STCreateTableUtil {

    private static final Logger LOGGER = Logger.getLogger("splittable");

    private static final String CREATE_DATA_CHANGE_TABLE_SQL =
            "CREATE TABLE "+ STDBConstants.DATA_CHANGE_TABLE_NAME +" (\n" +
            " id bigint identity(1,1) NOT NULL ,\n" +
            " event_type VARCHAR(32) NOT NULL,\n" +
            " table_name VARCHAR(64) NOT NULL,\n" +
            " data_id_column VARCHAR(64) NOT NULL,\n" +
            " data_id VARCHAR(64) NOT NULL,\n" +
            " synced CHAR(1) NOT NULL,\n" +
            " create_time VARCHAR(64) NOT NULL,\n" +
            " sync_time VARCHAR(64) NULL,\n" +
            " PRIMARY KEY (id)\n" +
            ")";

    private static final String CREATE_DATA_CLEAR_TABLE_SQL =
            "CREATE TABLE "+ STDBConstants.DATA_CLEAR_TABLE_NAME +" (\n" +
            " id bigint identity(1,1) NOT NULL ,\n" +
            " table_name VARCHAR(64) NOT NULL,\n" +
            " data_id_column VARCHAR(64) NOT NULL,\n" +
            " data_id VARCHAR(64) NOT NULL,\n" +
            " synced CHAR(1) NOT NULL,\n" +
            " clear_time VARCHAR(64) NOT NULL,\n" +
            " sync_time VARCHAR(64) NULL,\n" +
            " PRIMARY KEY (id)\n" +
            ")";

    //创建阈值表
    private static final String CREATE_DATA_THRESHOLD_TABLE_SQL =
            "CREATE TABLE " + STDBConstants.DATA_THRESHOLD_TABLE_NAME + "(\n" +
                    " id bigint IDENTITY(1,1) NOT NULL,\n" +
                    " requestid_threshold int NOT NULL,\n" +
                    " createdate_threshold VARCHAR(64) NOT NULL,\n" +
                    " module VARCHAR(64) NOT NULL,\n" +
                    " updatetime VARCHAR(64) NOT NULL,\n" +
                    " PRIMARY KEY (id)\n" +
                    ")\n";

    @Override
    public String createDataChangeTable() {
        return CREATE_DATA_CHANGE_TABLE_SQL;
    }

    @Override
    public String createDataClearTable() {
        return CREATE_DATA_CLEAR_TABLE_SQL;
    }

    @Override
    public String createDataThresholdTable() {
        return CREATE_DATA_THRESHOLD_TABLE_SQL;
    }


    @Override
    public String createFullDataTable(String tableName) {
        return createFullDataTableSqlserver(tableName);
    }

    public String createFullDataTableSqlserver(String tableName) {
        ConnectionPool connectionPool = ConnectionPool.getInstance();

        String fullDataTableName = tableName + "_all";
        String sql = "";
        String selectSql = "select top 1 * from " + tableName;
        String getKeySql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME ='"+ tableName +"'";
        String keyColumnName = "";
        WeaverConnection connection = null;
        ResultSet rs = null;
        WeaverStatement st = null;
        try {
            connection = connectionPool.getConnection();
            st = (WeaverStatement) connection.createStatement();
            rs = st.originExecuteQuery(getKeySql);
            while (rs.next()) {
                keyColumnName = rs.getString("COLUMN_NAME");
            }
            rs = st.originExecuteQuery(selectSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            StringBuffer columnBuilder = new StringBuffer();
            //循环处理字段
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                String columnType = rsmd.getColumnTypeName(i);
                int columnDisplaySize = rsmd.getColumnDisplaySize(i);
                int nullable = rsmd.isNullable(i);
                String nullStr = nullable == 0 ? "NOT NULL":"NULL";
                if ("char".equals(columnType.toLowerCase()) || "varchar".equals(columnType.toLowerCase())) {
                    columnBuilder.append(columnName).append(" " + columnType + "("+ columnDisplaySize +") ").append(nullStr).append(",");
                } else {
                    columnBuilder.append(columnName).append(" " + columnType + " ").append(nullStr).append(",");
                }
            }
            if (StringUtils.isNotBlank(keyColumnName)) {
                columnBuilder.append("PRIMARY KEY ("+ keyColumnName +")");
            } else {
                columnBuilder.deleteCharAt(columnBuilder.length()-1);
            }
            sql = "create table " + fullDataTableName + " (" + columnBuilder.toString() + ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ";
        } catch (Exception e) {
            LOGGER.error("generate create sqlserver full data table sql error:", e);
        } finally {
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            connectionPool.returnConnection(connection);
        }

        return sql;
    }
}
